# 本页用于数据库的创建和访问
import sqlite3
from tkinter import messagebox
from collections import namedtuple


class Database:
    name = 'data/data.sqlite'
    connect = sqlite3.connect(name)
    cursor = connect.cursor()

    def is_table_not_exist(self, name):
        self.cursor.execute(f"SELECT name FROM sqlite_master WHERE type='table' AND name='{name}'")
        if self.cursor.fetchone() is None:
            return True
        else:
            return False

    def create_table(self, name, sql):
        if self.is_table_not_exist(name):
            self.cursor.execute(sql)

    # def __del__(self):
    #     print("database object destroyed!")
    #     self.cursor.close()
    #     self.connect.close()


class Subject(Database):
    def __init__(self):
        super().__init__()
        self.id = None
        self.title = None
        self.mf = None
        self.state = True
        self.sql = '''create table subject(
                    id integer primary key autoincrement,
                    title varchar(20) unique,
                    jc varchar(20) unique,
                    mf integer,
                    state boolean
                    )'''
        self.create_table('subject', self.sql)
        self.instance = namedtuple('subject', ['id', 'title', 'jc', 'mf', 'state'])

    def translate_to_tuple(self, data: list):
        ret = []
        for item in data:
            ret.append(self.instance(*item))
        return ret

    def get(self, pr: int):
        self.cursor.execute(f"select * from subject where id={pr}")
        return self.cursor.fetchone()

    def all(self):
        self.cursor.execute(f"select * from subject")
        ls = self.cursor.fetchall()
        return self.translate_to_tuple(ls)

    def find(self, condition: str):
        self.cursor.execute(f"select * from subject where {condition}")
        return self.translate_to_tuple(self.cursor.fetchall())

    def create(self, title, jc, mf, state):
        print("create")
        try:
            self.cursor.execute(f"insert into subject (title, jc, mf, state) values ('{title}', '{jc}','{mf}', '{state}')")
            self.connect.commit()
        except Exception as e:
            messagebox.showerror(str(e), "创建错误，请检查是否命名冲突！")

    def update(self, ids, title, jc, mf, state):
        try:
            self.cursor.execute(f"update subject set title='{title}', jc= '{jc}', mf='{mf}',state='{state}' where id={ids}")
            self.connect.commit()
        except Exception as e:
            print(e)

    def delete(self, index):
        try:
            self.cursor.execute(f"delete from subject where id={index}")
            self.connect.commit()
        except Exception as e:
            print(e)



